Solution: Create Dependent Table

As we saw in the chapter Jaywalking, the best solution is to create a dependent table with one column for the multivalued attribute. Store the multiple values in multiple rows instead of multiple columns. Moreover, define a foreign key in the dependent table to associate the values to the key’s parent row in the Bugs table.

Patterns among antipatterns

In this lesson, we will create a dependent table to deal with multivalued attributes.

Creating a dependent table#

Here we are creating a Tags table that contains tags for the bugs and stores the information related to the bugs.

Let’s run these queries in the following playground to see the effects on the database.

Searching for bugs given a specific tag#

When all the tags associated with a bug are in a single column, then searching for bugs with a given tag is more straightforward.

Here we are searching for a tag using JOIN for querying from both the tables, Bugs and Tags.

Searching for a bug that relates to two specific tags#

Even more complex searches, such as a bug that relates to two specific tags, are easy to read.

Add or remove association#

We can add or remove an association more easily using a dependent table than with the Multi-column Attributes antipattern – just insert or delete a row from the dependent table. There’s no need to inspect multiple columns to see where we can add value.

See the following code for inserting values in the Tags table:

We can try it ourselves in the following code widget to see the effects on the database.

Here is the code to delete a row to remove association:

Let’s run the query in the following playground to see the working.

No duplication is allowed#

The PRIMARY KEY constraint ensures that no duplication is allowed. A given tag can be applied to a given bug only once. If we attempt to insert a duplicate, SQL returns a duplicate key error.

We’re not limited to three tags per bug, as we were when the Bugs table had three tagN columns. Now we can apply as many tags per bug as you need.